package com.rytong.tools.datastorage;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.rytong.tools.ui.LPWebView.MyLPWebView;
import com.rytong.tools.utils.Utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class SqlDb extends SQLiteOpenHelper {

    public static String DATABASE_NAME = "database.sql";
    public static int DATABASE_VERSION = 1;
    public static String TABLE_NAME = "databuffer";
    public static String FIELD_id = "_id";
    public final static String FIELD_VERSION = "version";
    public final static String FIELD_TEXT = "todo_text";
    public final static String FIELD_DATE = "date";
    public static String COLUMN_ID = "_id";
    public static String COLUMN_NAME = "name";
    public static String COLUMN_VALUE = "value";

    public SqlDb(Context context, String name, CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    // Called when the database is created for the first time
    public void onCreate(SQLiteDatabase db) {
        /* 创建table */
        // StringBuffer sql = new StringBuffer();
        // sql.append("CREATE TABLE ");
        // sql.append(TABLE_NAME);
        // sql.append(" (");
        // sql.append(FIELD_id);
        // // sql.append(" INTEGER primary key autoincrement, ");
        // sql.append(" , ");
        // sql.append(FIELD_VERSION);
        // sql.append(" text, ");
        // sql.append(FIELD_TEXT);
        // sql.append(" text, ");
        // sql.append(FIELD_DATE);
        // sql.append(" text)");
        // db.execSQL(sql.toString());

        db.execSQL("create table ".concat(TABLE_NAME).concat("(").concat(COLUMN_ID)
                .concat(" integer primary key autoincrement,").concat(COLUMN_NAME).concat(" varchar(20),")
                .concat(COLUMN_VALUE).concat(" varchar(100))"));

    }

    @Override
    // Called when the database needs to be upgraded.
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        StringBuffer sql = new StringBuffer();
        sql.append("DROP TABLE IF EXISTS ");
        sql.append(TABLE_NAME);
        db.execSQL(sql.toString());
        onCreate(db);
    }

    public static void setSQlAttribute(String dbName, int dbVersion, String tableName, String cID, String cName,
            String cValue) {
        DATABASE_NAME = dbName;
        DATABASE_VERSION = dbVersion;
        TABLE_NAME = tableName;
        COLUMN_ID = cID;
        COLUMN_NAME = cName;
        COLUMN_VALUE = cValue;
    }

    public Cursor select() {
        SQLiteDatabase db = this.getReadableDatabase();
        // This interface provides random read-write access to the result set returned by a database query
        /*
         * public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String
         * groupBy, String having, String orderBy) table The table name to compile the query against. columns A list of
         * which columns to return.Passing null will return all columns, which is discouraged to prevent reading data
         * from storage that isn't going to be used. selection A filter declaring which rows to return, formatted as an
         * SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
         * selectionArgs You may include ?s in selection, which will be replaced by the values from selectionArgs, in
         * order that they appear in the selection. The values will be bound as Strings. groupBy A filter declaring how
         * to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause
         * the rows to not be grouped. having A filter declare which row groups to include in the cursor, if row
         * grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will
         * cause all row groups to be included, and is required when row grouping is not being used. orderBy How to
         * order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use
         * the default sort order, which may be unordered.
         */
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

        return cursor;

    }

    public long insert(String fileId, String version, byte[] value) {
        long row = 0;
        try{
            SQLiteDatabase db = this.getWritableDatabase();
            /* 将添加的值放入 ContentValues */
            ContentValues cv = new ContentValues();
            // 设置数据id域
            cv.put(FIELD_id, fileId);
            // 设置数据版本域
            cv.put(FIELD_VERSION, version);
            // 设置数据值域
            cv.put(FIELD_TEXT, value);
            // 设置写入时间
            cv.put(FIELD_DATE, System.currentTimeMillis());

            // 写入数据库，返回数据所在行数
            row = db.insert(TABLE_NAME, null, cv);
        }catch(Exception e){
            Utils.printException(e);
        }
        return row;
    }

    /**
     * 将数据插入到数据库中（不判断当前key是否已存在，可能出现两个key同时存在的情况）
     * 
     * @param key
     * @param value
     */
    public void insertData(MyLPWebView ewv, String key, String value, String callback) {
        try{
            SQLiteDatabase database = getWritableDatabase();
            ContentValues contentValues = new ContentValues();
            contentValues.put(COLUMN_NAME, key);
            contentValues.put(COLUMN_VALUE, value);
            database.insert(TABLE_NAME, null, contentValues);
            ewv.loadUrl("javascript:".concat(callback).concat("();"));
        }catch(Exception e){
            Utils.printException(e);
        }
    }

    /**
     * 将数据插入到数据库中(不判断当前key是否已存在，可能出现两个key同时存在的情况)
     * 
     * @param database
     * @param key
     * @param value
     */
    public void insertData(String key, String value) {
        try{
            SQLiteDatabase database = getWritableDatabase();
            ContentValues contentValues = new ContentValues();
            contentValues.put(COLUMN_NAME, key);
            contentValues.put(COLUMN_VALUE, value);
            database.insert(TABLE_NAME, null, contentValues);
        }catch(Exception e){
            Utils.printException(e);
        }
    }

    /**
     * 向数据库中添加数据
     * 
     * @param key
     * @param value
     * @param callback
     */
    public void addData(MyLPWebView ewv, String key, String value, String callback) {
        insertData(ewv, key, value, callback);
    }

    /**
     * 向指定数据库插入数据
     * 
     * @param key
     * @param value
     */
    public void addData(String key, String value) {
        try{
            SQLiteDatabase database = getWritableDatabase();
            ContentValues contentValues = new ContentValues();
            contentValues.put(COLUMN_NAME, key);
            contentValues.put(COLUMN_VALUE, value);
            database.insert(TABLE_NAME, null, contentValues);
        }catch(Exception e){
            Utils.printException(e);
        }
    }

    /**
     * 从数据库中获取数据
     * 
     * @param database
     * @param key
     * @return 查到的数据
     */
    public String getData(String key) {
        SQLiteDatabase database = this.getReadableDatabase();
        Cursor cursor = database.rawQuery("select " + COLUMN_VALUE + " from " + TABLE_NAME + " where " + COLUMN_NAME
                + " = ?", new String[] { key });
        // select value from emp_table where name = "fwzx.xml";
        String value = null;
        try{
            while (cursor.moveToNext()) {
                value = cursor.getString(cursor.getColumnIndex(COLUMN_VALUE));
            }
        }catch(Exception e){
            Utils.printException(e);
        }finally{
            cursor.close();
            database.close();
        }
        return value;
    }

    public void getData(MyLPWebView ewv, String key, String callback) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select value from emp_table where name = ?", new String[] { key });
        // select value from emp_table where name = "fwzx.xml";
        String value = null;
        try{
            if (cursor.moveToNext()) {
                value = cursor.getString(cursor.getColumnIndex(COLUMN_VALUE));
                ewv.loadUrl("javascript:".concat(callback).concat("(").concat(value).concat(");"));
            }
        }catch(Exception e){
            Utils.printException(e);
        }finally{
            cursor.close();
            db.close();
        }
    }

    public void delete(String id) {
        try{
            SQLiteDatabase db = this.getWritableDatabase();
            String where = FIELD_id + " = ?";
            String[] whereValue = { id };
            db.delete(TABLE_NAME, where, whereValue);
        }catch(Exception e){
            Utils.printException(e);
        }
    }

    /**
     * 删除指定id对应的数据
     * 
     * @param db
     * @param id
     */
    public boolean deleteData(String key) {
        boolean flag = false;
        try{
            SQLiteDatabase database = getWritableDatabase();
            String where = COLUMN_NAME + " = ?";
            String[] whereValue = { key };
            int deletecount = database.delete(TABLE_NAME, where, whereValue);
            
            if (deletecount > 0) {
                flag = true;
            }
        }catch(Exception e){
            Utils.printException(e);
        }
        return flag;
    }

    /**
     * 覆盖数据库中已有数据
     * 
     * @param key
     * @param value
     */
    public void updateData(MyLPWebView ewv, String key, String value, String callback) {
        try{
            SQLiteDatabase db = this.getWritableDatabase();
            String where = FIELD_id + " = ?";
            String[] whereValue = { key };
            /* 将修改的值放入ContentValues */
            ContentValues cv = new ContentValues();
            // 更新数据值域
            cv.put(FIELD_TEXT, value);
            // 设置更新时间
            cv.put(FIELD_DATE, System.currentTimeMillis());

            db.update(TABLE_NAME, cv, where, whereValue);
            ewv.loadUrl("javascript:".concat(callback).concat("();"));
        }catch(Exception e){
            Utils.printException(e);
        }
    }

    /**
     * 覆盖数据库中已有数据
     * 
     * @param database
     * @param key
     * @param value
     */
    public void updateData(String key, String value) {
        try{
            SQLiteDatabase database = this.getWritableDatabase();
            String where = COLUMN_NAME + "=?";
            String[] whereValue = { key };
            /* 将修改的值放入ContentValues */
            ContentValues cv = new ContentValues();
            // 更新数据值域
            cv.put(COLUMN_VALUE, value);
            // 更新数据库字段
            database.update(TABLE_NAME, cv, where, whereValue);
        }catch(Exception e){
            Utils.printException(e);
        }
    }

    public void update(String id, String version, byte[] value) {
        try{
            SQLiteDatabase db = this.getWritableDatabase();
            String where = FIELD_id + " = ?";
            String[] whereValue = { id };
            /* 将修改的值放入ContentValues */
            ContentValues cv = new ContentValues();
            // 更新数据版本域
            cv.put(FIELD_VERSION, version);
            // 更新数据值域
            cv.put(FIELD_TEXT, value);
            // 设置更新时间
            cv.put(FIELD_DATE, System.currentTimeMillis());

            db.update(TABLE_NAME, cv, where, whereValue);
        }catch(Exception e){
            Utils.printException(e);
        }
    }

    public void open() {
        getWritableDatabase();
    }

    /**
     * 打开数据库，如果没有则建立一个新的数据库。（只建立数据库，但不建立表）
     * 
     * @param name
     * @return
     */
    public SQLiteDatabase open(String name) {
        try {
            return SQLiteDatabase.openDatabase(name, null, SQLiteDatabase.CREATE_IF_NECESSARY);
        } catch (SQLiteException e) {
            Utils.printException(e);
            return null;
        }
    }

    /**
     * 关闭数据库
     * 
     * @param o
     */
    public void close(Object o) {
        if (o instanceof SQLiteDatabase) {
            SQLiteDatabase sql = (SQLiteDatabase) o;
            if(sql.isOpen()){
                sql.close();
            }
        }
    }

    /**
     * 在提供的数据库中执行sql语句
     * 
     * @param handle
     * @param sql
     * 
     * @return 执行出错时候的错误信息。
     */
    public String exec(Object handle, String sql) {
    	String errorCode = null;
        try{
            if (handle instanceof SQLiteDatabase) {
                SQLiteDatabase temp = (SQLiteDatabase) handle;
                temp.execSQL(sql);
            }
        } catch (SQLiteException e){
            errorCode = e.getMessage();
        } catch(Exception e){
            errorCode = e.getMessage();
            Utils.printException(e);
        }
        return errorCode;
    }
    
    /**
     * 返回查询结果列表
     * @param SQLiteDatabase
     * @param sql
     * */
    public List<Map<String,String>> executeQuery(Object obj , String sql){
        List<Map<String,String>> resultList = new ArrayList<Map<String,String>>();
        if(obj instanceof SQLiteDatabase){
            
            SQLiteDatabase db = (SQLiteDatabase) obj;
            Cursor cursor = null;
            try {
                cursor = db.rawQuery(sql, null);
                
                while(cursor.moveToNext()){
                    Map<String,String> map = new HashMap<String, String>();
                    for(int i = 0 ; i < cursor.getColumnCount(); i++){
                        String columnName = cursor.getColumnName(i);
                        map.put(columnName, cursor.getString(i));
                    }
                    resultList.add(map);
                }
            } catch (Exception e) {
                Utils.printException(e);
            }
        }
        return resultList;
    }
}
